
USE Lib    
GO 

/*------------------------------------------------------------------------------------------------
BEGIN MAINTENANCE WRAPPER
-----------------------------*/
	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_table_definition' AND ROUTINE_SCHEMA = 'tsql') BEGIN
			DROP PROCEDURE tsql.sp_table_definition	
			PRINT 'DROP PROCEDURE tsql.sp_table_definition - completed with success.'
		END
/*-----------------------------
END MAINTENANCE WRAPPER
-------------------------------------------------------------------------------------------------*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			tsql.sp_table_definition 
Type:			stored procedure 
Owner:			
Project:			
Description:	Returns the definition of a given table 
Parameters:		@def_table_name = name of table to return the definition for 
				@def_schema_name = schema name
				@def_db_name = name of the table db   
				@out_table_name = replace table name in output 
				@out_schema_name = replace schema name in output
				@out_db_name = name of the output table db 
				@out_file_group = file group namr in output 
				@pk_flag = output pk in definition
				@debug = turns on and off display of internal variables/parameters 

Dependens on:  	none
Usage:			EXEC sp_table_definition sysssislog, dbo, newlog, dbi, 1
--------------------------------------------------------------------------------------------------
Version:		20120618
SrcCtrlApp:		
SrcCtrlPath:	
--------------------------------------------------------------------------------------------------
History:		06/18/2012 - Adrian E Dudau - Created procedure
================================================================================================*/
CREATE PROCEDURE tsql.sp_table_definition 
	(  @def_table_name SYSNAME 
	 , @def_schema_name SYSNAME = 'dbo' 
	 , @def_db_name SYSNAME = NULL 
	 , @out_table_name SYSNAME = NULL 
	 , @out_schema_name SYSNAME = NULL
	 , @out_db_name SYSNAME = NULL 
	 , @out_file_group SYSNAME = 'PRIMARY'
	 , @pk_flag BIT = 1
	 , @debug BIT = 0
	 , @definition NVARCHAR(MAX) = '' OUTPUT 
	)

AS

BEGIN /*FUNCTION*/
	DECLARE @CrLf AS CHAR(2);
	DECLARE @Tab AS CHAR(1); 

	DECLARE @SQL AS NVARCHAR(MAX) 
	DECLARE @Cnt AS BIGINT 

	-- Initialize variables
	SET @CrLf = CHAR(13) + CHAR(10) 
	SET @Tab = CHAR(9) 
	
	SET @SQL = ''
	SET @Cnt = 0

	-- Dynamic SQL used to change the Db context accordingly 
	-- If is not partition scheme add [] around filegroup
	SET @SQL = 'SELECT @CntInr = COUNT(*) FROM ' + @def_db_name + '.sys.partition_schemes ps WHERE ' + @out_file_group + ' LIKE ''%'' + ps.name + ''%'''; 
	EXECUTE sp_executesql @SQL, N'@CntInr BIGINT OUTPUT',@CntInr = @Cnt OUTPUT;

	IF @Cnt = 0 AND ISNULL(@out_file_group, '') <> '' BEGIN 
		SET @out_file_group = '[' + REPLACE(REPLACE(@out_file_group,'[',''),']','') + ']' 
	END

	-- If table does not exist raise error
	SET @SQL = 'SELECT @CntInr = COUNT(*) FROM ' + @def_db_name + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''' + @def_table_name + ''' AND TABLE_SCHEMA = ''' + @def_schema_name + '''';
	EXECUTE sp_executesql @SQL,N'@CntInr BIGINT OUTPUT',@CntInr = @Cnt OUTPUT;

	IF @Cnt = 0 BEGIN 
		SET @definition = 'Error! Table ' + @def_schema_name + '.' + @def_table_name + ' does not exist! Please check if the name is correct or you have the right permissions.'
		RETURN -1
	END /*IF {table does not exist}*/ 


	SET @SQL = ''

	-- If table exist extract definition
	IF @Cnt > 0 BEGIN 
		-- Declare inner variables
		SET @SQL = @SQL + 'DECLARE @Buffer AS VARCHAR(MAX); '  + @CrLf
		SET @SQL = @SQL + 'DECLARE @ColDef AS NVARCHAR(4000); ' + @CrLf
		SET @SQL = @SQL + 'DECLARE @CixDef AS NVARCHAR(4000); ' + @CrLf
		SET @SQL = @SQL + 'DECLARE @ColID AS INT; ' + @CrLf + @CrLf
		
		-- cursor to get table columns definition 
		SET @SQL = @SQL + 'DECLARE c_ColDef CURSOR LOCAL READ_ONLY FAST_FORWARD ' + @CrLf  
		SET @SQL = @SQL + 'FOR SELECT DISTINCT TOP 100 PERCENT ' + @CrLf 
		SET @SQL = @SQL + '			c.column_id	' + @CrLf
		-- add comma to fields other than first
		SET @SQL = @SQL + '		  , [SQL] = CASE WHEN c.column_id = 1  THEN ''  '' ELSE '' ,'' END ' + @CrLf
		-- get column name
		SET @SQL = @SQL + '			+ ''['' + c.name + '']'' ' + @CrLf
		-- get computed column definition	 
		SET @SQL = @SQL + '			+ CASE WHEN c.is_computed = 1 THEN '' AS '' + cc.[definition] + CASE WHEN cc.is_persisted = 1 THEN '' PERSISTED '' ELSE '''' END ELSE '''' END ' + @CrLf  
		-- get datatype	 
		SET @SQL = @SQL + '			+ CASE WHEN c.is_computed = 0 THEN '' '' + UPPER(st.name) ELSE '''' END ' + @CrLf 
		-- get data length for char type field (non-computed only)	 
		SET @SQL = @SQL + '			+ CASE WHEN c.is_computed = 0 AND UPPER(st.name) IN(''CHAR'', ''NCHAR'', ''NVARCHAR'', ''VARBINARY'', ''VARCHAR'') ' + @CrLf
		SET @SQL = @SQL + '				THEN ''('' + REPLACE(CAST(c.max_length / CASE WHEN UPPER(st.name) IN(''NCHAR'', ''NVARCHAR'') THEN 2 ELSE 1 END AS VARCHAR),''-1'',''MAX'') + '')'' ELSE '''' END ' + @CrLf
		-- get data length for date/time type field (non-computed only) 	 
		SET @SQL = @SQL + '			+ CASE WHEN c.is_computed = 0 AND UPPER(st.name) IN(''DATETIME2'', ''DATETIMEOFFSET'', ''TIME'') ' + @CrLf
		SET @SQL = @SQL + '				THEN ''('' + CAST(c.scale AS VARCHAR) + '')'' ELSE '''' END ' + @CrLf
		-- get data length for numeric type field (non-computed only) 	 
		SET @SQL = @SQL + '			+ CASE WHEN c.is_computed = 0 AND UPPER(st.name) IN(''DECIMAL'', ''NUMERIC'') ' + @CrLf
		SET @SQL = @SQL + '				THEN ''('' + CAST(c.[precision] AS VARCHAR) + '','' + CAST(c.scale AS VARCHAR) + '')'' ELSE '''' END ' + @CrLf
		-- get IDENTITY column definition 
		SET @SQL = @SQL + '			+ CASE WHEN c.is_identity = 1 THEN '' IDENTITY ('' + CAST(ic.seed_value AS VARCHAR) + '','' + CAST(ic.increment_value AS VARCHAR) + '')'' ELSE '''' END ' + @CrLf									  
		-- get NULLABLE options (non-computed only/non-persistent only) 	 
		SET @SQL = @SQL + '			+ CASE WHEN c.is_computed = 0 OR (c.is_computed = 1 AND cc.is_persisted = 1) ' + @CrLf 
		SET @SQL = @SQL + '				THEN CASE WHEN c.is_nullable = 0 THEN '' NOT'' ELSE '''' END + '' NULL''' + @CrLf 
		SET @SQL = @SQL + '			ELSE '''' ' + @CrLf 
		SET @SQL = @SQL + '		END ' + @CrLf
		SET @SQL = @SQL + 'FROM sys.columns c ' + @CrLf
		SET @SQL = @SQL + 'INNER JOIN sys.tables t ' + @CrLf
		SET @SQL = @SQL + '	ON c.[object_id] = t.[object_id] ' + @CrLf
		SET @SQL = @SQL + 'INNER JOIN sys.schemas s ' + @CrLf 
		SET @SQL = @SQL + '	ON s.[schema_id] = t.[schema_id] ' + @CrLf	
		SET @SQL = @SQL + 'INNER JOIN sys.types st ' + @CrLf 
		SET @SQL = @SQL + '	ON c.system_type_id = st.system_type_id ' + @CrLf 	
		SET @SQL = @SQL + '	AND c.user_type_id = st.user_type_id ' + @CrLf
		SET @SQL = @SQL + 'LEFT OUTER JOIN sys.identity_columns ic ' + @CrLf 
		SET @SQL = @SQL + '	ON c.[object_id] = ic.[object_id] ' + @CrLf 
		SET @SQL = @SQL + '	AND c.column_id = ic.column_id ' + @CrLf	
		SET @SQL = @SQL + 'LEFT OUTER JOIN sys.computed_columns cc ' + @CrLf 
		SET @SQL = @SQL + '	ON c.[object_id] = cc.[object_id] ' + @CrLf
		SET @SQL = @SQL + '	AND c.column_id = cc.column_id ' + @CrLf	
		SET @SQL = @SQL + 'WHERE t.name = ''' + @def_table_name + '''' + @CrLf 
		SET @SQL = @SQL + '	AND s.name = ''' + @def_schema_name + '''' + @CrLf
		SET @SQL = @SQL + 'ORDER BY c.column_id ' + @CrLf + @CrLf 	

		SET @SQL = @SQL + 'OPEN c_ColDef;' + @CrLf
		SET @SQL = @SQL + 'GOTO FETCH_TABLE_ROW;' + @CrLf + @CrLf
			
-- FETCH_TABLE_ROW: label
		SET @SQL = @SQL + 'FETCH_TABLE_ROW: ' + @CrLf
		SET @SQL = @SQL + '	FETCH NEXT FROM c_ColDef INTO @ColID, @ColDef; ' + @CrLf
		
		-- parse columns and store definition in variable	
		SET @SQL = @SQL + '	WHILE @@FETCH_STATUS = 0 BEGIN ' + @CrLf
		SET @SQL = @SQL + '		SET @Buffer = @Buffer + ' + @Tab + ' + @ColDef ; ' + @CrLf
		SET @SQL = @SQL + '		GOTO FETCH_TABLE_ROW; ' + @CrLf
		SET @SQL = @SQL + '	END ' + @CrLf + @CrLf

		SET @SQL = @SQL + '	CLOSE c_ColDef ' + @CrLf
		SET @SQL = @SQL + '	DEALLOCATE c_ColDef; ' + @CrLf + @CrLf
	
		-- if column definition was found 
		SET @SQL = @SQL + 'IF ISNULL(@ColDef, '''') <> '''' BEGIN ' + @CrLf
		-- add create table statement
		SET @SQL = @SQL + '	SET @Script = ''CREATE TABLE [''' + @out_schema_name + '''].[''' + @out_table_name + '''] (''' + @CrLf 
		SET @SQL = @SQL + '		+ @Buffer ' + @CrLf  

		IF @pk_flag = 1 BEGIN 
			--If table has primary key add the create table statement 
			SET @SQL = @SQL + 'IF EXISTS(SELECT * '
			SET @SQL = @SQL + '			FROM sys.indexes i '
			SET @SQL = @SQL + '			INNER JOIN sys.tables t ' 
			SET @SQL = @SQL + '				ON i.[object_id] = t.[object_id] ' 
			SET @SQL = @SQL + '			INNER JOIN sys.schemas s ' 
			SET @SQL = @SQL + '				ON s.[schema_id] = t.[schema_id] '
			SET @SQL = @SQL + '			WHERE NULL IS NULL '
			SET @SQL = @SQL + '				AND i.is_primary_key = 1 '
			SET @SQL = @SQL + '				AND t.name = ''' + @def_table_name + ''''  
			SET @SQL = @SQL + '				AND s.name = ''' + @def_schema_name + ''') BEGIN '
		
			SET @SQL = @SQL + '				SET @Buffer = '''' '
			-- extract index columns info 
			SET @SQL = @SQL + '			DECLARE c_CixDef CURSOR LOCAL READ_ONLY FAST_FORWARD '
			SET @SQL = @SQL + '				FOR SELECT c.column_id '
			SET @SQL = @SQL + '						 , [SQL] = CASE WHEN ixc.key_ordinal = 1  THEN ''  '' ELSE '' ,'' END '
			SET @SQL = @SQL + '								 + ''['' +  c.name + '']'' + CASE WHEN ixc.is_descending_key = 0 THEN '' ASC'' ELSE '' DESC'' END ' + @CrLf
			SET @SQL = @SQL + '					FROM sys.indexes i '
			SET @SQL = @SQL + '					INNER JOIN sys.tables t '
			SET @SQL = @SQL + '						ON i.[object_id] = t.[object_id] '  
			SET @SQL = @SQL + '					INNER JOIN sys.schemas s '
			SET @SQL = @SQL + '						ON s.[schema_id] = t.[schema_id] '
			SET @SQL = @SQL + '					INNER JOIN sys.index_columns ixc '
			SET @SQL = @SQL + '						ON i.index_id = ixc.index_id '
			SET @SQL = @SQL + '						AND i.[object_id]  = ixc.[object_id] '
			SET @SQL = @SQL + '					INNER JOIN sys.columns c '
			SET @SQL = @SQL + '						ON ixc.column_id = c.column_id '
			SET @SQL = @SQL + '						AND i.[object_id] = c.[object_id] '	 
			SET @SQL = @SQL + '					WHERE NULL IS NULL '
			SET @SQL = @SQL + '						AND t.name = ''' + @def_table_name + '''' 	
			SET @SQL = @SQL + '						AND s.name = ''' + @def_schema_name + ''''
			SET @SQL = @SQL + '						AND i.is_primary_key = 1 '
			SET @SQL = @SQL + '						AND i.is_disabled = 0 '
			SET @SQL = @SQL + '					ORDER BY i.index_id '
			SET @SQL = @SQL + '						   , ixc.is_included_column '
			SET @SQL = @SQL + '						   , ixc.key_ordinal '
			SET @SQL = @SQL + '						   , ixc.index_column_id '     	 

			SET @SQL = @SQL + '			OPEN c_CixDef; '
			SET @SQL = @SQL + '			GOTO FETCH_PK_ROW; '

-- FETCH_PK_ROW: 						
			SET @SQL = @SQL + 'FETCH_PK_ROW: '
			SET @SQL = @SQL + '	FETCH NEXT FROM c_CixDef INTO @ColID ,@CixDef; '
						
			-- parse columns and store definition in variable	
			SET @SQL = @SQL + '	WHILE @@FETCH_STATUS = 0 BEGIN '
			SET @SQL = @SQL + '		SET @Buffer = @Buffer + ' + @Tab + ' + @CixDef ' 
			SET @SQL = @SQL + '		GOTO FETCH_PK_ROW; '
			SET @SQL = @SQL + '	END '

			SET @SQL = @SQL + 'CLOSE c_CixDef; '
			SET @SQL = @SQL + 'DEALLOCATE c_CixDef; '
			-- format SQL for PK definition 
			SET @SQL = @SQL + '	IF ISNULL(@CixDef,'''') <> '''' BEGIN ' 
			SET @SQL = @SQL + '		SELECT @Script = @Script + '', CONSTRAINT [PK_' + @out_table_name + '] PRIMARY KEY '''  
			SET @SQL = @SQL + '							+ CASE WHEN i.type_desc = ''CLUSTERED'' THEN ''CLUSTERED '' ELSE ''NONCLUSTERED '' END  + ''(''' + @CrLf 
			SET @SQL = @SQL + '							+ @Buffer ' 
			SET @SQL = @SQL + '									+ '')''' + @CrLf 
			SET @SQL = @SQL + '							+ ''WITH (PAD_INDEX = '' + CASE WHEN i.is_padded = 0 THEN ''OFF'' ELSE ''ON'' END ' + @CrLf 
			SET @SQL = @SQL + '							+ ''     ,STATISTICS_NORECOMPUTE = OFF'' ' + @CrLf 
			SET @SQL = @SQL + '							+ ''     ,IGNORE_DUP_KEY = '' + CASE WHEN i.[ignore_dup_key] = 0 THEN ''OFF'' ELSE ''ON'' END ' + @CrLf 
			SET @SQL = @SQL + '							+ ''     ,ALLOW_ROW_LOCKS = '' + CASE WHEN i.[allow_row_locks] = 0 THEN ''OFF'' ELSE ''ON'' END ' + @CrLf 
			SET @SQL = @SQL + '							+ ''     ,ALLOW_PAGE_LOCKS = '' + CASE WHEN i.[allow_page_locks] = 0 THEN ''OFF'' ELSE ''ON'' END ' + @CrLf 
			SET @SQL = @SQL + '							+ '') ''' +  CASE WHEN ISNULL(@out_file_group,'''') <> '''' THEN 'ON ' + @out_file_group ELSE '' END + @CrLf 	
			SET @SQL = @SQL + '		FROM sys.indexes i '
			SET @SQL = @SQL + '		INNER JOIN sys.tables t ' 
			SET @SQL = @SQL + '			ON i.[object_id] = t.[object_id] '  
			SET @SQL = @SQL + '		INNER JOIN sys.schemas s ' 
			SET @SQL = @SQL + '			ON s.[schema_id] = t.[schema_id] ' 
			SET @SQL = @SQL + '		WHERE i.is_primary_key = 1 '
			SET @SQL = @SQL + '			AND t.name = ''' + @def_table_name + ''''  
			SET @SQL = @SQL + '			AND s.name = ''' + @def_schema_name + ''''
			SET @SQL = @SQL + 'END ' /*IF ISNULL(@CixDef,'') <> ''*/
			SET @SQL = @SQL + 'END'   /*If table has primary key*/		
			END 						

			SET @SQL = @SQL + 'SET @Script = @Script + '') ''' +  CASE WHEN ISNULL(@out_file_group,'') <> '' THEN 'ON ' + @out_file_group ELSE '' END + @CrLf
			SET @SQL = @SQL + 'END	' /*IF {column definition not empty}*/
	
			SET @SQL = @SQL + '	ELSE BEGIN ' 
			SET @SQL = @SQL + '		SET @Script = ''Error! Table ''' + @def_schema_name + '.' + @def_table_name + ''' does not exist! Please check if the name is correct or you have the right permissions.'''
			SET @SQL = @SQL + '	END	'/*ELSE if column definiton is empty*/
		END /*ELSE {table does not exist}*/		

	-- Return the result of the procedure
	EXECUTE sp_executesql @SQL,N'@Script NVARCHAR(MAX) OUTPUT',@Script = @definition OUTPUT;
	RETURN 0 

END /*FUNCTION*/
GO 

/*------------------------------------------------------------------------------------------------
BEGIN MAINTENANCE WRAPPER
-----------------------------*/
	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_table_definition' AND ROUTINE_SCHEMA = 'tsql') BEGIN
		PRINT '(RE)CREATE PROCEDURE tsql.sp_scriptout_table - completed with success.'
		
	END
	ELSE BEGIN
		PRINT '(RE)CREATE PROCEDURE tsql.sp_table_definition - FAILED!'
	END
/*-----------------------------
END MAINTENANCE WRAPPER
-------------------------------------------------------------------------------------------------*/


